package com.turing.post.transaction.impl;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.turing.post.report.util.IncomeDaySettlement;
import com.turing.post.transaction.util.MchAccDetail;
import com.turing.post.transaction.util.MchAccDetailDao;
import com.turing.post.util.GeneralDao;
import com.turing.post.util.PCPSApplicationException;
import com.turing.post.util.PCPSUtil;
import com.turing.post.util.PCPSWebInput;

@Stateless(mappedName = "transaction.impl.MchAccDetailDaoImpl")
@Remote
@Local
public class MchAccDetailDaoImpl implements MchAccDetailDao {
	/**
	 * 实体管理器
	 * 
	 * @see javax.persistence.EntityManager
	 */
	@PersistenceContext
	protected EntityManager _em;

	/**
	 * 插入
	 */
	public void insert(MchAccDetail mad) throws PCPSApplicationException {
		GeneralDao.insert(_em, mad);
	}

	/**
	 * 根据商户号、交易日期、交易流水号检索商户卡交易记帐明细
	 * 
	 * @param merchantNo
	 * @param tranDate
	 * @param tranSeqNo
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<MchAccDetail> getAllMchAccDetailByTranSeqNo(String merchantNo,
			Date tranDate, String tranSeqNo) {
		String sql = "select * from MchAccDetail where merchantNo = ?1 and tranDate = ?2 and tranSeqNo = ?3";
		Query query = _em.createNativeQuery(sql, MchAccDetail.class);
		query.setParameter(1, merchantNo);
		query.setParameter(2, tranDate);
		query.setParameter(3, tranSeqNo);
		List<MchAccDetail> madlist = query.getResultList();
		return madlist;
	}

	private BigInteger getValue(Object obj) {
		if (obj == null)
			return BigInteger.ZERO;
		return new BigInteger(obj.toString());
	}

	/**
	 * 根据商户号、帐务日期产生商户与销售机构的收益按日汇总数据
	 * 
	 * @param merchantNo
	 * @param tranDate
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<IncomeDaySettlement> getAllIncomeDaySettlementByMerchantNo(
			String merchantNo, Date tranDate) {
		String sql = "select t.openBranchNo, sum(t.paidAmount), sum(t.depositAmount), count(distinct t.tranSeqNo), sum( case when t.depositAmount <> 0 then 1 else 0 end ) "
				+ "from MchAccDetail t where t.merchantNo = ?1 and t.tranDate = ?2 "
				+ "group by t.openBranchNo";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, merchantNo);
		query.setParameter(2, tranDate);
		List<Object[]> obj_list = query.getResultList();
		if (obj_list == null)
			return null;

		// 类型转换
		List<IncomeDaySettlement> ids_list = new ArrayList<IncomeDaySettlement>();
		for (Object[] objarr : obj_list) {
			IncomeDaySettlement ids = new IncomeDaySettlement();
			BigInteger depositCount = getValue(objarr[4]);
			ids.setSaleBranchNo(objarr[0].toString());
			ids.setConsume(getValue(objarr[1]));
			ids.setDeposit(getValue(objarr[2]));
			ids.setConsumeCount(getValue(objarr[3]).subtract(depositCount));
			ids.setDepositCount(depositCount);
			ids_list.add(ids);
		}

		return ids_list;
	}

	/**
	 * 判断一定时间范围内是否存在交易记录
	 * 
	 * @param merchantNo
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public boolean merchantHasTransaction(String merchantNo, Date startDate,
			Date endDate) {
		String sql = "select min(t.tranSeqNo) from MchAccDetail t "
				+ "where t.merchantNo = ?1 and t.tranDate between ?2 and ?3 ";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, merchantNo);
		query.setParameter(2, startDate);
		query.setParameter(3, endDate);

		Object result = query.getSingleResult();
		if (result == null)
			return false;

		if (PCPSUtil.isEmpty(result.toString()))
			return false;

		return true;
	}

	/**
	 * 持卡人消费情况表
	 * 
	 * @return List<Object[]>
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getMchAccDetailAll(PCPSWebInput webInput,
			String startDate, String endDate, BigInteger amount) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo()))
			return null;
		String issuecompanyno = PCPSUtil.getIssueCompanyNo(webInput) + "%";
		String sql = "select d.merchantname,e.name,e.email,e.linktel,c.cardclassicname,sum(a.PaidAmount) as amount"
				+ " from MchAccDetail a,ActivateCard b,merchant d,CardClassic c,PersonalCustom e"
				+ " where a.cardno=b.cardno "
				+ " and a.merchantno=d.merchantno"
				+ " and b.cardclassicid=c.cardclassicid"
				+ " and b.ownerid=e.personalid"
				+ " and b.salebranchno like ?1"
				+ " and a.trandate>= to_date(?2,'yyyy-MM-dd')"
				+ " and a.trandate<= to_date(?3,'yyyy-MM-dd')"
				+ " group by d.merchantname,e.name,e.email,e.linktel,c.cardclassicname"
				+ " having sum(a.PaidAmount)>=?4"
				+ " order by amount desc,d.merchantname,c.cardclassicname";

		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issuecompanyno);
		query.setParameter(2, startDate);
		query.setParameter(3, endDate);
		query.setParameter(4, amount);
		List<Object[]> obj_list = query.getResultList();
		return obj_list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getMchAccDetailDakTotale(PCPSWebInput webInput,
			String startDate, String endDate,String merchantNo) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo())
				|| PCPSUtil.isEmpty(startDate) || PCPSUtil.isEmpty(endDate))
			return null;
		String sql = " select c.salebranchno,b.Businesstype,nvl(count(*),0),  " +
			" nvl(sum(a.PaidAmount),0),nvl(sum(a.FeeAmount),0) " +
			" from MchAccDetail a ,MchBsTypeTradDetail b ,ActivateCard c " +
			" where a.merchantno = ?1 and a.merchantno = b.merchantno " +
			" and a.trandate = b.trandate and a.transeqno = b.transeqno " +
			" and a.cardno = c.cardno "+
			" and a.trandate >= to_date(?2,'yyyy-mm-dd') " +
			" and a.trandate <= to_date(?3,'yyyy-mm-dd') " +
			" group by c.salebranchno,b.Businesstype ";
		
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, merchantNo);
		query.setParameter(2, startDate);
		query.setParameter(3, endDate);
		List<Object[]> obj_list = query.getResultList();
		return obj_list;
	}
}